﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'P_SetCollationForAll')
	BEGIN
		DROP  Procedure  esf_sso.P_SetCollationForAll
	END

GO


CREATE Procedure esf_sso.P_SetCollationForAll
AS

	DECLARE @tableSchema	VARCHAR (MAX)
	DECLARE @tableName		VARCHAR (MAX)
	DECLARE @columnName			VARCHAR (MAX)
	DECLARE @columnMaxLength	VARCHAR (MAX)
	DECLARE @columnIsNullable	VARCHAR (MAX)
	DECLARE @alter				VARCHAR (MAX)
	

	DECLARE curTC CURSOR FOR 
		SELECT 
			SchemaName
			,TableName
			,ColumnName
			,CASE ColumnIsNullable
				WHEN 1 THEN 'NULL'
				ELSE 'NOT NULL'
			END
			,ColumnMaxLength
		FROM
			esf_sso.VTableColumns
		WHERE 
			ColumnType = 'varchar'
			AND ColumnCollationName <> 'Modern_Spanish_CI_AI'
			AND SchemaName <> 'esf_sso'
	
	OPEN curTC
	
	FETCH NEXT FROM curTC INTO
		@tableSchema, @tableName, @columnName, @columnIsNullable, @columnMaxLength
			
	WHILE @@FETCH_STATUS = 0
	BEGIN
		
		BEGIN TRY
			
			SET @alter = 'ALTER TABLE ' + @tableSchema + '.[' + @tableName + 
				'] ALTER COLUMN ' + @columnName + ' varchar (' + CONVERT (VARCHAR (100), @columnMaxLength) + ') ' + 
					' COLLATE Modern_Spanish_CI_AI ' + @columnIsNullable
	
		--	print (@alter)
	
			EXEC (@alter)
		
		END TRY
		BEGIN CATCH
	
			print 'ERROR: ' + @alter
			print ERROR_MESSAGE()

		END CATCH

		FETCH NEXT FROM curTC INTO
			@tableSchema, @tableName, @columnName, @columnIsNullable, @columnMaxLength
	
	END
	
	CLOSE curTC
	DEALLOCATE curTC

GO